﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using NanjingbeiRailwayStation.Core;
using NanjingbeiRailwayStation.Data.ClickHouse.Models;
using NanjingbeiRailwayStation.WebApi.Models;
using static System.Runtime.InteropServices.JavaScript.JSType;

namespace NanjingbeiRailwayStation.Data.ClickHouse.Services
{
    /// <summary>
    /// 
    /// </summary>
    public class EventReportService
    {

      /// <summary>
      /// 
      /// </summary>
      /// <param name="imei"></param>
      /// <returns></returns>
        public async Task<EventNotification?> GetLastEventNotification(string imei)
        {
            using var db = new NanjingbeiRailwayStation.Data.ClickHouse.Db();

            string sql = string.Format(@"SELECT  ta.imei as Imei, 
ta.eventName  as EventName,
tb.DeviceName  as DeviceName,
tb.DeviceClass as  DeviceClass,
tb.InstallationLocation  as InstallationLocation,
tb.InstallationPosition as InstallationPosition,
ta.eventTime  as EventTime,
tb.DeviceName as DeviceName,
tb.Installer  as Installer,
tb.InstallerTelphone  as InstallerTelphone,
tb.Director as Director,
tb.DirectorTelphone  as DirectorTelphone
from  jindujiangong.EventReport as  ta 
join jindujiangong.DeviceEntitie as tb
on ta.imei =tb.imei 
where ta.imei ='{0}'
order by ta.eventTime  desc 
limit 1", imei);

            var entitiy = await db.Database.SqlQueryRaw<EventNotification>(sql).FirstOrDefaultAsync();

            return entitiy;
        }

        
        /// <summary>
        /// 
        /// </summary>
        /// <param name="beginDateTime"></param>
        /// <param name="endDateTime"></param>
        /// <returns></returns>

        public async Task<List<AlarmInfo>> GetAlarmList(DateTime beginDateTime,DateTime endDateTime)
        {
            var startDay = beginDateTime.ToString("yyyy-MM-dd HH:mm:ss");
            var endDay = endDateTime.ToString("yyyy-MM-dd HH:mm:ss");

            using var db = new NanjingbeiRailwayStation.Data.ClickHouse.Db();

            var sql = string.Format(@"select eventTime, eventName, ta.imei as imei ,VolA,VolB,VolC,CurA,CurB,CurC,Ta,Tb,Tc,Tn ,DeviceName,DeviceMode,InstallationLocation,Longitude,Latitude,InstallationPosition from EventReport as ta
                    join DeviceEntitie as tb
                    on ta.imei =tb.imei 
                    where ta.eventTime  >=toDateTime('{0}') and ta.eventTime <=toDateTime('{1}')  and  endsWith(ta.eventName,'Alarm')", startDay,endDay);
            var entitiy = await db.Database.SqlQueryRaw<AlarmInfo>(sql).ToListAsync();

            return entitiy;
             
        }
         

        /// <summary>
        /// 
        /// </summary>
        /// <param name="imei"></param>
        /// <param name="minute"></param>
        /// <returns></returns>
        public async Task<List<DeviceAlarmCount>> GetRecentNumberOfAlarmsOfAllDevice(int minute = 10)
        {
            using var db = new NanjingbeiRailwayStation.Data.ClickHouse.Db();
            var sql = string.Format(@"select imei ,count(*) as count
                        from jindujiangong.EventReport
                        where  endsWith(eventName,'Alarm')
                        and eventTime>(date_add(now(), INTERVAL 0 HOUR)-INTERVAL 10 minute)
                        group by imei");

            var entity = await db.Database.SqlQueryRaw<DeviceAlarmCount>(sql).ToListAsync();
            return entity;

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="imei"></param>
        /// <param name="minute"></param>
        /// <returns></returns>
        public async Task<ulong> GetRecentNumberOfAlarms(string imei,int minute=10)
        {
            using var db = new NanjingbeiRailwayStation.Data.ClickHouse.Db();
            var sql = string.Format(@"select count(*) as count
                        from jindujiangong.EventReport
                        where  endsWith(eventName,'Alarm')
                        and eventTime>(date_add(now(), INTERVAL 0 HOUR)-INTERVAL {0} minute)
                        and imei='{1}'", minute, imei);

            var entity = await db.Database.SqlQueryRaw<QueryCount>(sql).FirstAsync();

            return entity.count;
            
        }
        /// <summary>
        /// 是否报警
        /// </summary>
        /// <param name="imei"></param>
        /// <param name="minute"></param>
        /// <returns></returns>

        public async Task<bool> IsOnAlarms(string imei, int minute = 10)
        {
            var cnt = await GetRecentNumberOfAlarms(imei, minute);

            return cnt > 0;

        }
     

        /// <summary>
        /// 事件上报
        /// </summary>
        /// <param name="imei"></param>
        /// <param name="eventName"></param>
        /// <param name="dateTimeBegin"></param>
        /// <param name="dateTimeEnd"></param>
        /// <returns></returns>

        public async Task<List< MeterEventReport>> GetEventReport(string imei,string eventName,DateTime dateTimeBegin, DateTime dateTimeEnd)
        {
            using var db = new NanjingbeiRailwayStation.Data.ClickHouse.Db();
            var startDayOfMonth = dateTimeBegin.ToString("yyyy-MM-dd HH:mm:ss");
            var endDayOfMonth = dateTimeEnd.ToString("yyyy-MM-dd HH:mm:ss");
             
            string sql = string.Format(@"select * from jindujiangong.EventReport
                            where imei='{0}' and eventName='{1}'
                            and eventTime >=toDateTime('{2}') and eventTime<=toDateTime('{3}')
                            and eventName <>'I_UnblanceAlarm'", imei, eventName, startDayOfMonth, endDayOfMonth);

            var entities = await db.Database.SqlQueryRaw<MeterEventReport>(sql).ToListAsync();

            return entities;
        }

        /// <summary>
        /// 时间
        /// </summary>
        /// <returns></returns>
        public async Task<ClickHouseDateTime> GetClickHouseDateTime()
        {
            using var db = new NanjingbeiRailwayStation.Data.ClickHouse.Db();

            var sql = @"select now() as DateTime;";

            var entity = await db.Database.SqlQueryRaw<ClickHouseDateTime>(sql).FirstAsync();

            return entity;
        }


        

        /// <summary>
        /// 今日报警次数
        /// </summary>
        /// <returns></returns>
        public async Task<TodayAlarmFrequency?> GetTodayAlarmFrequency()
        {
            using var db = new NanjingbeiRailwayStation.Data.ClickHouse.Db();

            var sql = @"select sumMerge(eventCount ) as eventCount 
                        from jindujiangong.EventReport_Alarm_Settlement_By_Day
                        where toYYYYMMDD(eventTime)=toYYYYMMDD(now())";


            var entity = await db.Database.SqlQueryRaw<TodayAlarmFrequency>(sql).FirstOrDefaultAsync();

            return entity;
            
        }

        

        /// <summary>
        /// 
        /// </summary>
        /// <param name="size"></param>
        /// <returns></returns>
         public async Task<ulong> GetTowerCraneAlarmCount()
        {
            using var db = new NanjingbeiRailwayStation.Data.ClickHouse.Db();

            var sql = string.Format(@"                            select 
                            count(*) as count
                            from jindujiangong.EventReport as ta
                            join DeviceEntitie  as tb final
                            on ta.imei =tb.imei
                            where toYYYYMMDD( eventTime ) =toYYYYMMDD(now())
                        ");

            var entitys = await db.Database.SqlQueryRaw<QueryCount>(sql).FirstAsync();

            return entitys.count;

        }


        /// <summary>
        /// 塔吊报警数据(今日的）
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public async Task<List<TowerCraneAlarmData>> GetTowerCraneAlarmDataByPage(int pageIndex, int pageSize)
        {
            var m = pageIndex * pageSize;
            using var db = new NanjingbeiRailwayStation.Data.ClickHouse.Db();

            var sql = string.Format(@"select 
                                    ta.imei
                                        ,ta.eventName
                                        ,ta.eventTime
                                        ,tb.DeviceName as deviceName
                                        ,tb.InstallationLocation as installationLocation
                            from jindujiangong.EventReport as ta
                            join DeviceEntitie  as tb final
                            on ta.imei =tb.imei
                            where toYYYYMMDD( eventTime ) =toYYYYMMDD(now())
                            and endsWith(eventName,'Alarm')
                            order by eventTime desc
                            limit {0},{1}
                            ", m,pageSize);


            var entitys = await db.Database.SqlQueryRaw<TowerCraneAlarmData>(sql).ToListAsync();


            return entitys;


        }

        /// <summary>
        /// 塔吊报警数据（今日的）
        /// </summary>
        /// <param name="size"></param>
        /// <returns></returns>

        public async Task<List<TowerCraneAlarmData>> GetTowerCraneAlarmData(int size = 100)
        {
            using var db = new NanjingbeiRailwayStation.Data.ClickHouse.Db();

            var sql = string.Format(@"select 
                                    ta.imei
                                        ,ta.eventName
                                        ,ta.eventTime
                                        ,tb.DeviceName as deviceName
                                        ,tb.InstallationLocation as installationLocation
                            from jindujiangong.EventReport as ta
                            join DeviceEntitie  as tb final
                            on ta.imei =tb.imei
                            where toYYYYMMDD( eventTime ) =toYYYYMMDD(now())
                            and endsWith(eventName,'Alarm')
                            order by eventTime desc
                            limit {0}", size);


            var entitys = await db.Database.SqlQueryRaw<TowerCraneAlarmData>(sql).ToListAsync();


            return entitys;
 
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="size"></param>
        /// <returns></returns>

        public async Task<List<MonitoringOfAultyEquipment>> GetMonitoringOfAultyEquipment(int size = 100)
        {
            using var db = new NanjingbeiRailwayStation.Data.ClickHouse.Db();

            var sql = string.Format(@"select 
                                        ta.imei
                                        ,ta.eventName
                                        ,ta.eventTime
                                        ,tb.DeviceName as deviceName
                                        ,tb.InstallationLocation as installationLocation
                                        from jindujiangong.EventReport as ta
                                        join jindujiangong.DeviceEntitie  as tb final
                                        on ta.imei =tb.imei
                                        where toYYYYMMDD( eventTime ) =toYYYYMMDD(date_add(now(), INTERVAL 0 HOUR))
                                        order by eventTime desc
                                        limit {0}", size);


            var entitys = await db.Database.SqlQueryRaw<MonitoringOfAultyEquipment>(sql).ToListAsync();

            return entitys;
          
        }


        /// <summary>
        /// 告警类型分布
        /// </summary>
        /// <param name="size"></param>
        /// <returns></returns>
        public async Task<List<AlarmTypeDistribution>> GetAlarmTypeDistribution(int size = 100)
        {
            using var db = new NanjingbeiRailwayStation.Data.ClickHouse.Db();

            var sql = string.Format(@"
                                select eventName
                                ,count(*) as eventCount
                                from jindujiangong.EventReport
                                where toYYYYMMDD( eventTime) =toYYYYMMDD(now())
                                and endsWith(eventName,'Alarm')
                                group by eventName
                            limit {0}", size);


            var entitys = await db.Database.SqlQueryRaw<AlarmTypeDistribution>(sql).ToListAsync();

            return entitys;
        }


    }
}
